﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Create PROCEDURE SelectProductsFromSubcategories
	@categoryId int,
	@productCount int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	declare @cnt int
	declare @rowNumber int
	SET NOCOUNT ON;
	
    select @cnt = count (*) from Products where CategoryId = @categoryId
	set @productCount = @productCount + @cnt
	declare @catCount int
    select @catCount = count (*) from Categories where Parent = @categoryId
	set @rowNumber = 0
	while @catCount > @rowNumber
	begin
		set @rowNumber = @rowNumber + 1
		declare @catId int
		select top 1 @catId = CategoryId from Categories where CategoryId = @categoryId
	end
	select @productCount as ProductsCount
	
END


